DROP PROCEDURE [dbo].[pr0053]
GO
----- Cap nhat lai  so du

CREATE  PROCEDURE [dbo].[pr0053]
	 @DivisionID varchar(20), 
	@WareHouseID  varchar(20),
	 @TranMonth  int, 
	@TranYear int, 
	@InventoryID  varchar(20), 
	@ConvertedAmount as Decimal(28,8), 
	@ConvertedQuantity as Decimal(28,8), 
	@DebitAccountID  varchar(20), 
	@CreditAccountID  varchar(20), 
	@Type as tinyint -- (1) la cap nhat tang,(0 ) la cap nhat giam

 AS

If @Type =1	 --- Cap nhat tang
	Begin
		--Print ' Nhan test'
		If not exists (Select 1 From tb0106 	Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity ,  DebitAmount , CreditQuantity ,
					CreditAmount,  EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, 0,0, @ConvertedQuantity, @ConvertedAmount , 0,0, 
				@ConvertedQuantity, @ConvertedAmount)
		else
			Update tb0106
				Set	 DebitQuantity 	=	isnull(DebitQuantity,0)	+ isnull(@ConvertedQuantity,0),
					DebitAmount 	=	isnull(DebitAmount,0)	+ isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		+ isnull(@ConvertedQuantity,0),
					EndAmount		=	isnull(EndAmount,0)		+ isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
					DivisionID =@DivisionID and
					WareHouseID =@WareHouseID and
					InventoryAccountID =@DebitAccountID and
					TranMonth =@TranMonth and
					TranYear =@TranYear	
	

	End

If @Type =0 		--- Cap nhat giam
	Begin
	If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity,  DebitAmount,  CreditQuantity,
					CreditAmount,   EndQuantity , EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@CreditAccountID, 0,0, 0,0,@ConvertedQuantity, @ConvertedAmount , 
				-@ConvertedQuantity, -@ConvertedAmount)
		else
			Update tb0106
				set 	CreditQuantity	 	=	isnull(CreditQuantity,0)		+ isnull(@ConvertedQuantity,0),
					CreditAmount 		=	isnull(CreditAmount,0)		+ isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		- isnull(@ConvertedQuantity,0),
					EndAmount		= 	isnull(EndAmount,0)	- 	isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID  =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	End

-------------------- Van chuyen Noi bo

If @Type =11	 --- Cap nhat tang
	Begin
		--Print ' Nhan test'
		If not exists (Select 1 From tb0106 	Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity ,  DebitAmount , CreditQuantity ,
					CreditAmount,  InDebitQuantity, InDebitAmount, EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, 0,0, @ConvertedQuantity, @ConvertedAmount , 0,0, @ConvertedQuantity, @ConvertedAmount , 
				@ConvertedQuantity, @ConvertedAmount)
		else
			Update tb0106

				Set	 DebitQuantity 	=	isnull(DebitQuantity,0)	+ isnull(@ConvertedQuantity,0),
					DebitAmount 	=	isnull(DebitAmount,0)	+ isnull(@ConvertedAmount,0),
					InDebitQuantity 	=	isnull(InDebitQuantity,0)	+ isnull(@ConvertedQuantity,0),
					InDebitAmount 	=	isnull(InDebitAmount,0)	+ isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		+ isnull(@ConvertedQuantity,0),
					EndAmount		=	isnull(EndAmount,0)		+ isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	

	End

If @Type =10 		--- Cap nhat giam
	Begin
	If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity,  DebitAmount,  CreditQuantity,
					CreditAmount,   InCreditQuantity, InCreditAmount,  EndQuantity , EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@CreditAccountID, 0,0, 0,0, @ConvertedQuantity, @ConvertedAmount , @ConvertedQuantity, @ConvertedAmount ,
				-@ConvertedQuantity, -@ConvertedAmount)
		else
			Update tb0106
				set 	CreditQuantity	 	=	isnull(CreditQuantity,0)		+ isnull(@ConvertedQuantity,0),
					CreditAmount 		=	isnull(CreditAmount,0)		+ isnull(@ConvertedAmount,0),
				 	InCreditQuantity	 	=	isnull(InCreditQuantity,0)		+ isnull(@ConvertedQuantity,0),
					InCreditAmount 		=	isnull(InCreditAmount,0)		+ isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		- isnull(@ConvertedQuantity,0),
					EndAmount		= 	isnull(EndAmount,0)	- 	isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID  =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	End


GO